﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using 简单三层架构.DBUtility;
using 简单三层架构.IDAL;
using 简单三层架构.Model;
using System.Collections.Generic;


namespace 简单三层架构.DAL
{
    public class Item : IItem
    {
        
        private const string SQL_INSERT_ITEM = "INSERT INTO Items(";
        private const string SQL_SELECT_ITEM = "SELECT * FROM Items";

        private const string PARM_ITEM_ID = "ItemID";
        private const string PARM_TITLE = "Title";
        private const string PARM_PRICE = "Price";
        private const string PARM_INTRODUCTION = "Introduction";
        private const string PARM_CATEGORY = "Category";
        private const string PARM_IMAGE = "Image";


        public List<ItemInfo> GetAllItems()
        {
            List<ItemInfo> items = new List<ItemInfo>();
            using (SqlConnection connection = SqlHelper.GetConnection())
            {
                DataSet dataSet = SqlHelper.GetDataSetBySqlCommand(SQL_SELECT_ITEM, connection);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    ItemInfo item = new ItemInfo();
                    item.ItemID = Convert.ToInt32(row[PARM_ITEM_ID]);
                    item.Title = row[PARM_TITLE].ToString();
                    item.Price = Convert.ToSingle(row[PARM_PRICE]);
                    item.Introduction = row[PARM_INTRODUCTION].ToString();
                    item.Category = Convert.ToInt32(row[PARM_CATEGORY]);
                    item.Image = row[PARM_IMAGE].ToString();
                    items.Add(item);
                }
            }
            return items;
        }

        public List<ItemInfo> GetItemsByCategory(int categoryId)
        {
            List<ItemInfo> items = new List<ItemInfo>();
            using (SqlConnection connection = SqlHelper.GetConnection())
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(SQL_SELECT_ITEM);
                builder.Append(" WHERE Category = ");
                builder.Append(categoryId);

                DataSet dataSet =
                    SqlHelper.GetDataSetBySqlCommand(builder.ToString(),
                                                     connection);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    ItemInfo item = new ItemInfo();
                    item.ItemID = Convert.ToInt32(row[PARM_ITEM_ID]);
                    item.Title = row[PARM_TITLE].ToString();
                    item.Price = Convert.ToSingle(row[PARM_PRICE]);
                    item.Introduction = row[PARM_INTRODUCTION].ToString();
                    item.Category = Convert.ToInt32(row[PARM_CATEGORY]);
                    item.Image = row[PARM_IMAGE].ToString();
                    items.Add(item);
                }
            }
            return items;
        }

        public List<ItemInfo> GetItemsBySearch(string keyword)
        {
            List<ItemInfo> items = new List<ItemInfo>();
            using (SqlConnection connection = SqlHelper.GetConnection())
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(SQL_SELECT_ITEM).Append(" WHERE Title LIKE '%").Append(keyword).Append("%';");

                DataSet dataSet =
                    SqlHelper.GetDataSetBySqlCommand(builder.ToString(),
                                                     connection);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    ItemInfo item = new ItemInfo();
                    item.ItemID = Convert.ToInt32(row[PARM_ITEM_ID]);
                    item.Title = row[PARM_TITLE].ToString();
                    item.Price = Convert.ToSingle(row[PARM_PRICE]);
                    item.Introduction = row[PARM_INTRODUCTION].ToString();
                    item.Category = Convert.ToInt32(row[PARM_CATEGORY]);
                    item.Image = row[PARM_IMAGE].ToString();
                    items.Add(item);
                }
            }
            return items;
        }

        public ItemInfo GetItemByID(int itemId)
        {
            ItemInfo item = new ItemInfo();
            using (SqlConnection connection = SqlHelper.GetConnection())
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(SQL_SELECT_ITEM);
                builder.Append(" WHERE ItemID = ");
                builder.Append(itemId);

                DataSet dataSet =
                    SqlHelper.GetDataSetBySqlCommand(builder.ToString(),
                                                     connection);

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    item.ItemID = Convert.ToInt32(row[PARM_ITEM_ID]);
                    item.Title = row[PARM_TITLE].ToString();
                    item.Price = Convert.ToSingle(row[PARM_PRICE]);
                    item.Introduction = row[PARM_INTRODUCTION].ToString();
                    item.Category = Convert.ToInt32(row[PARM_CATEGORY]);
                    item.Image = row[PARM_IMAGE].ToString();
                }
            }
            return item;
        }

        public void Insert(ItemInfo item)
        {
            using (SqlConnection connection = SqlHelper.GetConnection())
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(SQL_INSERT_ITEM);
                builder.Append(PARM_TITLE + "," + PARM_PRICE + "," + PARM_INTRODUCTION + "," + PARM_CATEGORY + "," +
                               PARM_IMAGE);
                builder.Append(") VALUES (");
                builder.Append(string.Format("'{0}',{1},'{2}',{3},'{4}", item.Title, item.Price, item.Introduction,
                                             item.Category, item.Image));
                builder.Append(")");


                SqlHelper.ExecuteNonQuerySqlCommand(builder.ToString(),
                                                    connection);
            }
        }
    }
}